import pymysql
connect = pymysql.connect(
    host='127.0.0.1',
    db='study',
    user='root',
    password='mysql',
    port=3306,
    charset='utf8', )
# 创建游标
cur = connect.cursor()


def result(x):
    for row in x:
        print(row)


# 3.查询
""""------------------------查询所有--------------------------------------"""
sql_all = 'select * from  Student'  # 查询所有
cur.execute(sql_all)
result(cur.fetchall())
print('------查询所有成功!----------')


""""---------------------------去重查询 distinct----------------------------"""
sql_distinct = 'select distinct com from Student'
cur.execute(sql_distinct)
result(cur.fetchall())
print('------去重查询成功!----------')


""""----------------------------条件查询 where-------------------------------"""
# 多条件查询
sql_where = 'select cno, name from Student where com="教师" and salary >1200'
cur.execute(sql_where)
print('职称为教师且工资大于1200的教师的教师号与姓名：')
result(cur.fetchall())

# 关键字between and： 在什么范围
sql_between = 'select name from Student where salary between 1500 and 7000'
cur.execute(sql_between)
print('工资在1500到7000的教师的教师姓名：')
result(cur.fetchall())

# 关键字is null (判断某个字段是否为null不能用等号，要用is)
sql_is = 'select name from Student where salary is null'
cur.execute(sql_is)
print('没有工资的教师姓名：')
result(cur.fetchall())

# 关键字in：集合查询
sql_in = 'select name from Student where salary in (1000,1888,3000)'
cur.execute(sql_in)
print('工资是1000，1888，,3000的教师的教师姓名：')
result(cur.fetchall())

# 关键字Like: 模糊查询
sql_like1 = 'select * from Student where name like "%三"'  # %占位符匹配多个字符
sql_like2 = 'select * from Student where name like "_三"'  # _占位符匹配一个字符
cur.execute(sql_like1)
print('名字以三结尾的教师：')
result(cur.fetchall())
cur.execute(sql_like2)
print('名字二个字且第二个字为三的教师：')
result(cur.fetchall())


"""---------------------------分组查询 group by----------------------------"""
print('---------group by--------')
sql_group = 'select count(cno),com from Student group by com'
cur.execute(sql_group)
result(cur.fetchall())


"""---------------------------聚合函数 count max min avg sum ----------------------------"""
print('教师平均工资:')
sql_avg = 'select avg(salary) from Student '
cur.execute(sql_avg)
result(cur.fetchall())


"""---------------------------having 过滤  Having发生在分组group by之后----------------------------"""
print('各职称年龄大于10，且平均工资大于1500的教师')
sql_having = 'select * from Student where age>10 group by com having avg(salary)>1500'
cur.execute(sql_having)
result(cur.fetchall())

"""---------------------------查询排序 order by----------------------------"""
print("年龄大于20，先按照工资降序排序，工资相同按照年龄升序排序：")
sql_order = 'select * from Student where age>20 order by salary desc,age'
cur.execute(sql_order)
result(cur.fetchall())


"""---------------------------Limit 限制查询条数----------------------------"""
print('limit查询：')
sql_order = 'select * from Student where age>20 order by salary desc,age limit 0,2'  # limit 0,2; 从第1条开始，共2条
cur.execute(sql_order)
result(cur.fetchall())

